const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

console.log('🔧 更新不完整的实例表结构');
console.log('============================');

try {
  // 1. 获取所有需要更新的实例表（除了已经完整的 cooling_water_spray_system）
  const incompleteTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    AND name != 'device_instances_piping_cooling_water_spray_system'
    ORDER BY name
  `).all();
  
  console.log(`📊 找到 ${incompleteTables.length} 个需要更新的实例表`);
  
  // 2. 获取设备类型映射
  const deviceMappings = db.prepare(`
    SELECT classification_name, specialty, naming_table, position_table, base_params_table
    FROM device_type_mappings
    ORDER BY specialty, classification_name
  `).all();
  
  console.log(`📋 加载 ${deviceMappings.length} 个设备类型映射`);
  
  // 3. 获取三个标准属性表的字段结构
  const namingFields = db.prepare(`PRAGMA table_info(table_c2)`).all()
    .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name));
  
  const positionFields = db.prepare(`PRAGMA table_info(table_c4)`).all()
    .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name));
  
  // table_c6 也是位置表，合并位置字段
  const positionFields2 = db.prepare(`PRAGMA table_info(table_c6)`).all()
    .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name));
  
  console.log(`📋 标准字段结构:`);
  console.log(`  命名表字段 (table_c2): ${namingFields.length}个`);
  console.log(`  位置表字段 (table_c4): ${positionFields.length}个`);
  console.log(`  位置表字段 (table_c6): ${positionFields2.length}个`);
  
  // 4. 为每个不完整的实例表创建更新方案
  let successCount = 0;
  let failCount = 0;
  
  for (const table of incompleteTables) {
    console.log(`\\n🔧 更新实例表: ${table.name}`);
    console.log('='.repeat(60));
    
    try {
      // 4.1 从表名推断设备类型和专业
      const tableNameParts = table.name.replace('device_instances_', '').split('_');
      const specialty = tableNameParts[0]; // piping, electrical, etc.
      
      // 4.2 专业名称映射（英文->中文）
      const specialtyMapping = {
        'piping': '配管专业',
        'electrical': '电气专业', 
        'hvac': '暖通专业',
        'communication': '通信专业',
        'instrumentation': '仪表专业'
      };
      
      const chineseSpecialty = specialtyMapping[specialty];
      
      // 查找对应的设备类型映射
      let deviceMapping = null;
      
      if (chineseSpecialty) {
        // 查找同专业的映射
        const specialtyMappings = deviceMappings.filter(m => m.specialty === chineseSpecialty);
        
        if (specialtyMappings.length > 0) {
          // 使用该专业的第一个映射作为默认
          deviceMapping = specialtyMappings[0];
          
          // 可以在这里添加更精确的匹配逻辑
          // 目前简单使用第一个映射
        }
      }
      
      if (!deviceMapping) {
        console.log(`  ❌ 未找到 ${specialty} 专业的设备类型映射，使用默认配置`);
        // 使用默认配置
        deviceMapping = {
          naming_table: 'table_c2',
          position_table: 'table_c4', 
          base_params_table: 'table_c2' // 默认使用命名表
        };
      } else {
        console.log(`  ✅ 使用设备映射: ${deviceMapping.classification_name}`);
        console.log(`     命名表: ${deviceMapping.naming_table}`);
        console.log(`     位置表: ${deviceMapping.position_table}`);
        console.log(`     基础参数表: ${deviceMapping.base_params_table}`);
      }
      
      // 4.3 获取基础参数表字段
      let baseParamsFields = [];
      try {
        baseParamsFields = db.prepare(`PRAGMA table_info(${deviceMapping.base_params_table})`).all()
          .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name));
        console.log(`  📊 基础参数表字段: ${baseParamsFields.length}个`);
      } catch (error) {
        console.log(`  ⚠️  无法获取基础参数表 ${deviceMapping.base_params_table}: ${error.message}`);
        // 使用默认字段
        baseParamsFields = [
          {name: '规格', type: 'TEXT'},
          {name: '型号', type: 'TEXT'},
          {name: '制造厂家', type: 'TEXT'}
        ];
      }
      
      // 4.4 生成新的表结构
      const newColumns = [];
      
      // 添加业务字段（如果不存在）
      const businessFields = [
        'device_type_code TEXT',
        'instance_number TEXT', 
        'object_number TEXT',
        'object_description TEXT'
      ];
      newColumns.push(...businessFields);
      
      // 添加命名表字段（naming_前缀）
      namingFields.forEach(field => {
        newColumns.push(`naming_${field.name} ${field.type}`);
      });
      
      // 添加位置表字段（position_前缀）
      positionFields.forEach(field => {
        newColumns.push(`position_${field.name} ${field.type}`);
      });
      
      // 如果位置表是table_c6，也添加其字段
      if (deviceMapping.position_table === 'table_c6') {
        positionFields2.forEach(field => {
          newColumns.push(`position_${field.name} ${field.type}`);
        });
      }
      
      // 添加基础参数表字段（base_params_前缀）
      baseParamsFields.forEach(field => {
        newColumns.push(`base_params_${field.name} ${field.type}`);
      });
      
      console.log(`  📊 新表结构总字段: ${newColumns.length + 3}个 (包括系统字段)`);
      
      // 4.5 创建新表和迁移数据
      const backupTableName = `${table.name}_backup_${Date.now()}`;
      
      // 备份原表
      db.prepare(`CREATE TABLE ${backupTableName} AS SELECT * FROM ${table.name}`).run();
      console.log(`  💾 已创建备份表: ${backupTableName}`);
      
      // 删除原表
      db.prepare(`DROP TABLE ${table.name}`).run();
      
      // 创建新表
      const createTableSQL = `
        CREATE TABLE ${table.name} (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          ${newColumns.join(',\\n          ')},
          created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
      `;
      
      db.prepare(createTableSQL).run();
      console.log(`  ✅ 已重建表结构`);
      
      // 迁移现有数据（如果有的话）
      const oldData = db.prepare(`SELECT * FROM ${backupTableName}`).all();
      if (oldData.length > 0) {
        console.log(`  🔄 迁移 ${oldData.length} 条现有数据...`);
        
        // 这里需要智能映射旧字段到新字段
        // 目前简单处理：只迁移基础业务字段
        const insertSQL = `
          INSERT INTO ${table.name} (device_type_code, instance_number, object_number, object_description)
          SELECT device_type_code, instance_number, object_number, object_description 
          FROM ${backupTableName}
        `;
        
        try {
          const result = db.prepare(insertSQL).run();
          console.log(`  ✅ 成功迁移 ${result.changes} 条记录`);
        } catch (migrateError) {
          console.log(`  ⚠️  数据迁移失败: ${migrateError.message}`);
        }
      } else {
        console.log(`  📊 原表无数据，跳过迁移`);
      }
      
      // 清理备份表（可选，保留用于安全）
      // db.prepare(`DROP TABLE ${backupTableName}`).run();
      
      successCount++;
      console.log(`  🎉 ${table.name} 更新完成`);
      
    } catch (error) {
      console.log(`  ❌ 更新 ${table.name} 失败: ${error.message}`);
      failCount++;
    }
  }
  
  // 5. 总结更新结果
  console.log('\\n📊 实例表更新总结');
  console.log('========================');
  console.log(`✅ 成功更新: ${successCount}个表`);
  console.log(`❌ 更新失败: ${failCount}个表`);
  console.log(`📊 总计处理: ${incompleteTables.length}个表`);
  
  if (successCount > 0) {
    console.log('\\n🎯 更新后的标准结构:');
    console.log('- 系统字段: id, created_at, updated_at');
    console.log('- 业务字段: device_type_code, instance_number, object_number, object_description');
    console.log(`- 命名字段: ${namingFields.length}个 naming_ 前缀字段`);
    console.log(`- 位置字段: ${positionFields.length}个 position_ 前缀字段`);
    console.log('- 基础参数字段: 变量个数 base_params_ 前缀字段');
  }
  
  if (failCount > 0) {
    console.log('\\n⚠️  请检查失败的表并手动处理');
  }
  
} catch (error) {
  console.error('❌ 更新脚本执行失败:', error.message);
} finally {
  db.close();
}